﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;
using System.Data;

namespace Yubis.Data.SqlServer
{
    public class FieldChoice : IFieldChoice
    {

        string _name;
        string _tableName;

        public FieldChoice(string name)
        {
            _name = name;
            _tableName = "Field_Choice$" + name;
        }

        public void SaveData(List<FieldChoiceData> data)
        {
            StringBuilder commandText  = new StringBuilder();

            foreach (FieldChoiceData fieldChoiceData in data)
            {
                if (fieldChoiceData.RecordType == RecordType.Delete)
                {
                    commandText.AppendLine(String.Format("DELETE FROM {0} WHERE Id = '{1}' ; ", _tableName, fieldChoiceData.Id));
                    commandText.AppendLine(String.Format("DELETE FROM Language_Translation WHERE ResourceId = '{0}'; ", fieldChoiceData.Id));
                }

                if (fieldChoiceData.RecordType == RecordType.Update )
                {
                    commandText.AppendLine(String.Format("DELETE FROM {0} WHERE Id = '{1}' ; ", _tableName, fieldChoiceData.Id));
                }
                if (fieldChoiceData.RecordType == RecordType.Update || fieldChoiceData.RecordType == RecordType.New)
                {
                    commandText.AppendLine(String.Format("INSERT INTO {0} (Id, [Order]) VALUES ('{1}', {2}) ; ", _tableName, fieldChoiceData.Id, fieldChoiceData.Order));
                }

                foreach (short localeId in fieldChoiceData.Labels.Keys)
                {
                    if (fieldChoiceData.RecordType == RecordType.Update)
                    {
                        commandText.AppendLine(String.Format("DELETE FROM Language_Translation WHERE ResourceId = '{0}' AND LocaleId = {1} ; ", fieldChoiceData.Id, localeId));
                    }
                    commandText.AppendLine(String.Format("INSERT INTO Language_Translation (ResourceId, LocaleId, Name) VALUES ('{0}', {1}, '{2}') ; ", fieldChoiceData.Id, localeId, fieldChoiceData.Labels[localeId]));

                }

            }

            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(Yubis.Data.Properties.Settings.Default.ConnectionString);
            command.CommandText = commandText.ToString();

            command.Connection.Open();
            command.ExecuteNonQuery();
        }

        public List<FieldChoiceData> GetData(int localeId = 0)
        {
            List<FieldChoiceData> returnValue = new List<FieldChoiceData>();

            SqlCommand command = new SqlCommand();
            command.Connection = new SqlConnection(Yubis.Data.Properties.Settings.Default.ConnectionString);
            if (localeId == 0)
            {
                command.CommandText = String.Format("SELECT Id, LocaleId, Name, [Order] FROM {0} INNER JOIN Language_Translation ON {0}.Id = Language_Translation.ResourceId ORDER BY [Order]", _tableName);
            }
            else
            {
                command.CommandText = String.Format("SELECT Id, LocaleId, Name, [Order] FROM {0} INNER JOIN Language_Translation ON {0}.Id = Language_Translation.ResourceId WHERE LocaleId = {1} ORDER BY [Order]", _tableName, localeId);
            }
            command.Connection.Open();
            SqlDataReader data = command.ExecuteReader();

            FieldChoiceData dataItem = null;

            if (data.HasRows)
            {
                while (data.Read())
                {
                    Guid id = Guid.Parse( data["Id"].ToString() );

                    IEnumerable<FieldChoiceData> search = (from item in returnValue where item.Id == id select item);

                    if (search.Count() > 0)
                    {
                        search.First().Labels.Add(Convert.ToInt16(data["LocaleId"]), Convert.ToString(data["Name"]));
                    }
                    else
                    {
                        dataItem = new FieldChoiceData();
                        dataItem.Id = id;
                        dataItem.RecordType = RecordType.Original;
                        dataItem.Labels.Add(Convert.ToInt16(data["LocaleId"]), Convert.ToString(data["Name"]));
                        returnValue.Add(dataItem);
                    }
                }
            }
            return returnValue;
      
        }
    
        public void Create()
        {
            ServerConnection connection = new ServerConnection();
            connection.ConnectionString = Yubis.Data.Properties.Settings.Default.ConnectionString;
            
            Server server = new Server(connection);
            server.ConnectionContext.Connect();

            Database database = server.Databases[server.ConnectionContext.SqlConnectionObject.Database];

            Table table = new Table(database, _tableName);

            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            table.Columns.Add(new Column(table, "Order", DataType.SmallInt));
            table.Indexes.Add(new Index(table, "PK_" + _tableName));
            table.Indexes["PK_" + _tableName].IndexKeyType = IndexKeyType.DriPrimaryKey;
            table.Indexes["PK_" + _tableName].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_" + _tableName], "Id"));
            table.Create();

            SqlCommand command = new SqlCommand();
            command.Connection = server.ConnectionContext.SqlConnectionObject;
            command.CommandText = "INSERT INTO Field_Choice ( Id, Name ) VALUES ( @Id, @Name )";
            
            command.Parameters.Add("@Id", SqlDbType.UniqueIdentifier);
            command.Parameters.Add("@Name", SqlDbType.NVarChar);

            command.Parameters["@Id"].Value = Guid.NewGuid();
            command.Parameters["@Name"].Value = _name;
            command.ExecuteNonQuery();

        }
    }
}
